﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DacSanMienTayOBJ
{
    public class CategoryBusiness
    {
        public static bool InsertCategory(Category cate,int isSection, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "";
                SqlParameter[] pa;
                if (isSection == 1)
                {
                    sql = "insert into category(ParentCategoryID,IsSection,CategoryName,CategoryImage,Metakeyword,IsVisible,Priority) values(Null,@isSection,@cateName,@cateImage,@metakey,@isVisible,@priority)";
                    pa = new SqlParameter[6];
                    pa[0] = new SqlParameter("@isSection", cate.IsSection);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.CategoryImage);
                    pa[3] = new SqlParameter("@metakey", cate.MetaKeyword);
                    pa[4] = new SqlParameter("@isVisible", cate.IsVisible);
                    pa[5] = new SqlParameter("@priority", cate.Priority);
                    
                }
                else
                {
                    sql = "insert into category(ParentCategoryID,IsSection,CategoryName,CategoryImage,Metakeyword,IsVisible,Priority) values(@parentCateID,@isSection,@cateName,@cateImage,@metakey,@isVisible,@priority)";
                    pa = new SqlParameter[7];
                    pa[0] = new SqlParameter("@isSection", cate.IsSection);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.CategoryImage);
                    pa[3] = new SqlParameter("@metakey", cate.MetaKeyword);
                    pa[4] = new SqlParameter("@isVisible", cate.IsVisible);
                    pa[5] = new SqlParameter("@priority", cate.Priority);
                    pa[6] = new SqlParameter("@parentCateID", cate.ParentCategoryID);
                }
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }

                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();

            }
        }

        public static bool UpdateCategory(int cateID, Category cate,int isSection, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "";
                SqlParameter[] pa;
                if (isSection==1)
                {
                    sql = "update category set CategoryName=@cateName,CategoryImage=@cateImage,Metakeyword=@metakey,IsSection=@isSection where CategoryID=@cateID";
                    pa = new SqlParameter[5];
                    pa[0] = new SqlParameter("@cateID", cateID);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.CategoryImage);
                    pa[3] = new SqlParameter("@metakey", cate.MetaKeyword);
                    pa[4] = new SqlParameter("@isSection", isSection);
                }
                else
                {
                    sql = "update category set ParentCategoryID=@parentCateID,CategoryName=@cateName,CategoryImage=@cateImage,Metakeyword=@metakey,IsSection=@isSection where CategoryID=@cateID";
                    pa = new SqlParameter[6];
                    pa[0] = new SqlParameter("@cateID", cateID);
                    pa[1] = new SqlParameter("@cateName", cate.CategoryName);
                    pa[2] = new SqlParameter("@cateImage", cate.CategoryImage);
                    pa[3] = new SqlParameter("@metakey", cate.MetaKeyword);
                    pa[4] = new SqlParameter("@parentCateID", cate.ParentCategoryID);
                    pa[5] = new SqlParameter("@isSection", isSection);
                }
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }

                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool UpdateVisibleCategory(int cateID, int visible, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "update category set IsVisible=@isVisible where CategoryID=@cateID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@cateID", cateID);
                pa[1] = new SqlParameter("@isVisible", visible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Category> ListAllCategory(ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.CategoryID,cate.ParentCategoryID,cate.CategoryName,cate.CategoryName as ParentCategoryName, cate.IsSection,cate.CategoryImage,cate.Metakeyword,cate.IsVisible,cate.Priority from category cate";
                SqlCommand command = helper.GetCommandNonParameter(sql, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.CategoryID = int.Parse(reader["CategoryID"].ToString());
                    cate.CategoryImage = reader["CategoryImage"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.IsSection = int.Parse(reader["IsSection"].ToString());
                    }
                    catch
                    {

                    }
                    cate.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    cate.MetaKeyword = reader["Metakeyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Category> ListAllCategoryFollowParent(int parentCateID,ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.CategoryID,cate.ParentCategoryID,cate.CategoryName,cate.CategoryName as ParentCategoryName, cate.IsSection,cate.CategoryImage,cate.Metakeyword,cate.IsVisible,cate.Priority from category cate where ParentCategoryID=@parentcateID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@parentcateID", parentCateID);
                SqlCommand command = helper.GetCommand(sql,pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.CategoryID = int.Parse(reader["CategoryID"].ToString());
                    cate.CategoryImage = reader["CategoryImage"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.IsSection = int.Parse(reader["IsSection"].ToString());
                    }
                    catch
                    {

                    }
                    cate.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    cate.MetaKeyword = reader["Metakeyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Category> ListAllCategoryFollowSection(int section, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.CategoryID,cate.ParentCategoryID,cate.CategoryName,cate.CategoryName as ParentCategoryName, cate.IsSection,cate.CategoryImage,cate.Metakeyword,cate.IsVisible,cate.Priority from category cate where IsSection=@isSection";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@isSection", section);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.CategoryID = int.Parse(reader["CategoryID"].ToString());
                    cate.CategoryImage = reader["CategoryImage"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.IsSection = int.Parse(reader["IsSection"].ToString());
                    }
                    catch
                    {

                    }
                    cate.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    cate.MetaKeyword = reader["Metakeyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Category> ListAllCategoryVisible(int visible, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Category> list = new List<Category>();
                string sql = "select cate.CategoryID,cate.ParentCategoryID,cate.CategoryName,cate.CategoryName as ParentCategoryName, cate.IsSection,cate.CategoryImage,cate.Metakeyword,cate.IsVisible,cate.Priority from category cate,category parent where IsVisible=@isVisible";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@isVisible", visible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cate = new Category();
                    cate.CategoryID = int.Parse(reader["CategoryID"].ToString());
                    cate.CategoryImage = reader["CategoryImage"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.IsSection = int.Parse(reader["IsSection"].ToString());
                    }
                    catch
                    {

                    }
                    cate.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    cate.MetaKeyword = reader["Metakeyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    cate.ParentCategoryName = reader["ParentCategoryName"].ToString();
                    list.Add(cate);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static Category GetCategoryDetail(int cateID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                Category cate = null;
                string sql = "select *from category where CategoryID=@cateID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@cateID", cateID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if(reader.Read())
                {
                    cate = new Category();
                    cate.CategoryID = int.Parse(reader["CategoryID"].ToString());
                    cate.CategoryImage = reader["CategoryImage"].ToString();
                    cate.CategoryName = reader["CategoryName"].ToString();
                    try
                    {
                        cate.IsSection = int.Parse(reader["IsSection"].ToString());
                    }
                    catch
                    {

                    }
                    cate.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    cate.MetaKeyword = reader["Metakeyword"].ToString();
                    try
                    {
                        cate.ParentCategoryID = int.Parse(reader["ParentCategoryID"].ToString());
                    }
                    catch
                    {

                    }
                    cate.Priority = int.Parse(reader["Priority"].ToString());
                    
                }
                return cate;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool InsertCategoryProduct(int cateID, int productID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "insert into categoryproduct(ProductID,CategoryID) values(@productID,@cateID)";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@productID", productID);
                pa[1] = new SqlParameter("@cateID", cateID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool DeleteCategoryProductFollowProductID(int productID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "delete categoryproduct where ProductID=@productID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@productID", productID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool DeleteCategoryProductFollowCategoryID(int cateID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "delete categoryproduct where CategoryID=@cateID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@cateID", cateID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }
    }
}
